/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package CRUD;

import Beans.BeanProntuario;
import Conexao.ConexaoBanco;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Marcos
 */
public class CRUDProntuario {
    
    public Connection conexao;

    public CRUDProntuario() throws SQLException {
        this.conexao = ConexaoBanco.getConexao();
    }
    
    public void adiciona(BeanProntuario BP) throws SQLException {
        String sql = "insert into prontuario(den_prontuario, cod_pessoa, dt_prontuario)" + "values(?,?,?)";
        PreparedStatement stmt = conexao.prepareStatement(sql);                
        stmt.setString(1, BP.getDen_prontuario());              
        stmt.setString(2, BP.getCod_pessoa());                          
        stmt.setDate(3, new java.sql.Date(BP.getDt_prontuario().getTime()));                          
        stmt.execute();
        stmt.close();
    }  
    
    public void altera(BeanProntuario BP) throws SQLException {
        String sql = "update prontuario set den_prontuario = ? "
                + " WHERE cod_prontuario = ? ";
        PreparedStatement stmt = conexao.prepareStatement(sql);
        stmt.setString(1, BP.getDen_prontuario());        
        stmt.setInt(2, BP.getCod_prontuario());     
        stmt.execute();
        stmt.close();
    }
    
    public void remove(BeanProntuario BP) throws SQLException {
        String sql = "delete from prontuario where den_prontuario = '' ";
        PreparedStatement stmt = conexao.prepareStatement(sql);        
        stmt.execute();
        stmt.close();
    }
    
    public List<BeanProntuario> getListaProntuario(String cod_pessoa) throws SQLException {
        String sql = "select * from prontuario where cod_pessoa = ? and den_prontuario <> ''"
                + "order by dt_prontuario";       

        PreparedStatement stmt = this.conexao.prepareStatement(sql);
        stmt.setString(1, cod_pessoa);
        ResultSet rs = stmt.executeQuery();
        List<BeanProntuario> ListaProntuario = new ArrayList<BeanProntuario>();
        while (rs.next()) {
            BeanProntuario BP = new BeanProntuario();
            BP.setCod_prontuario(rs.getInt("cod_prontuario"));
            BP.setDen_prontuario(rs.getString("den_prontuario"));                   
            BP.setDt_prontuario(rs.getDate("dt_prontuario"));                   
            ListaProntuario.add(BP);
        }
        rs.close();
        stmt.close();
        return ListaProntuario;
    }
    
    public List<BeanProntuario> getListaProntuarioMaxCodProntuario(String cod_pessoa) throws SQLException {
        String sql = "select max(cod_prontuario) cod_prontuario from prontuario where cod_pessoa = ? ";       

        PreparedStatement stmt = this.conexao.prepareStatement(sql);
        stmt.setString(1, cod_pessoa);
        ResultSet rs = stmt.executeQuery();
        List<BeanProntuario> ListaProntuario = new ArrayList<BeanProntuario>();
        while (rs.next()) {
            BeanProntuario BP = new BeanProntuario();
            BP.setCod_prontuario(rs.getInt("cod_prontuario"));                        
            ListaProntuario.add(BP);
        }
        rs.close();
        stmt.close();
        return ListaProntuario;
    }
}